﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using orienta.Models;
using System.Text;
using orienta.ViewModels;
using System.Collections;
using orienta.Helper;
using System.Configuration;
using orienta.Utils;
using System.IO;

namespace orienta.Controllers
{
    public class EstadisticaController : OrientaController
    {

        private GoodTireEntities db = new GoodTireEntities();



        public ActionResult Inicio()
        {
            // @Html.DropDownList("nIdeDepRegion", (IEnumerable<SelectListItem>)ViewBag.DepRegion, "--Seleccione--", new { onchange = "javascript:onchangeLimpiarDepPadre();" })

            HtmlUtils htmlUtils = new HtmlUtils();
            ViewBag.DepRegion = htmlUtils.getComboDependenciaRegion();
            ViewBag.programa = htmlUtils.getComboProgramaMidis();
            return ReturnPartialView("Inicio");
        }



        public FileResult Exportar_1()
        {


            // assuming file is a PDF
            byte[] bindata = System.IO.File.ReadAllBytes("C:\\proyectos\\Template.xlsx");
            return File(bindata, "application/vnd.ms-excel", "reporte.xlsx");
        }

        public FileResult Exportar()
        {
            String tipo = Request["tipo"];
            String fechaInicio = Request["fechaInicio"];
            String fechaFin = Request["fechaFin"];
            String region = Request["region"];
            String dependencia = Request["dependencia"];
            String[] etiquetas = null;
            String[] headers = null;
            IList<dynamic> lista = null;
            Boolean IsExportIncludesHeader = true;
            if ("1".Equals(tipo))/*por region - espacio atencion*/
            {
                etiquetas = new string[] { "region", "dependencia" };
                IsExportIncludesHeader = false;
                lista = consultarClaseRegionDependencia(fechaInicio, fechaFin, region, dependencia);

            }

            if ("2".Equals(tipo))/*por region*/
            {
                etiquetas = new string[] { "region" };
                lista = consultarClaseRegionSP(fechaInicio, fechaFin, region, dependencia);
            }
            if ("3".Equals(tipo))/*por region*/
            {
                lista = consultarProgramaClase(fechaInicio, fechaFin, region, dependencia);

                etiquetas = new string[] { "TIPO_DE_SOLICITUD" };

                headers = getColumnsTitulo(lista);

                IsExportIncludesHeader = true;
            }
            if ("4".Equals(tipo))/*por region*/
            {
                lista = consultarTipoSolicitudRegion(fechaInicio, fechaFin, region, dependencia);

                etiquetas = new string[] { "motivo_solicitud" };

                headers = getColumnsTitulo(lista);

                IsExportIncludesHeader = true;
            }
            if ("5".Equals(tipo))/*por region*/
            {
                etiquetas = new string[] { "region", "dependencia" };
                IsExportIncludesHeader = false;
                lista = consultarCasosRegionDependenciaSP(fechaInicio, fechaFin, region, dependencia);

                if (lista.Count > 0) {
                    dynamic ultimo = lista.ToList<dynamic>().Last();
                    ultimo.parcial = "";
                }
            }

            if ("6".Equals(tipo))/*por region*/
            {
                etiquetas = new string[] { "programa" };
                IsExportIncludesHeader = false;
                lista = consultarCasosProgramaSP(fechaInicio, fechaFin, region, dependencia);
                
            }
            if ("7".Equals(tipo))/*por region*/
            {
                etiquetas = new string[] { "region", "dependencia" };
                IsExportIncludesHeader = false;
                lista = consultarCasosResueltosEspacioAtencion(fechaInicio, fechaFin, region, dependencia);
                if (lista.Count > 0)
                {
                    dynamic ultimo = lista.ToList<dynamic>().Last();
                    ultimo.parcial = "";
                }
            }

            if ("8".Equals(tipo))/*por region*/
            {
                etiquetas = new string[] { "programa" };
                IsExportIncludesHeader = false;
                lista = consultarCasosResueltoPrograma(fechaInicio, fechaFin, region, dependencia);
            }
            if ("9".Equals(tipo))/*por casos URGENTES - programa */
            {
                etiquetas = new string[] { "programa" };
                IsExportIncludesHeader = false;
                lista = EstadisticaHelper.executeStore("usp_reporteCasosUrgentesSP", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });

                if (lista.Count > 0)
                {
                    dynamic ultimo = lista.ToList<dynamic>().Last();
                    ultimo.prioridad = "";
                }
            }

            if ("10".Equals(tipo))/*por casos URGENTES - programa */
            {
                etiquetas = new string[] { "region", "espacio" };
                IsExportIncludesHeader = false;
                lista = EstadisticaHelper.executeStore("usp_reporteCasosUrgentesEspacioSP", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });

                if (lista.Count > 0)
                {
                    dynamic ultimo = lista.ToList<dynamic>().Last();
                    ultimo.prioridad = "";
                }
            }

            if ("11".Equals(tipo))/*por casos URGENTES - programa */
            {
                etiquetas = new string[] { "descripcion" };
                IsExportIncludesHeader = false;
                lista = lista = consultarNumeroDias(fechaInicio, fechaFin, region, dependencia);
            }

            if ("12".Equals(tipo))/*por casos URGENTES - programa */
            {
                etiquetas = new string[] { "modalidad" };
                IsExportIncludesHeader = false;

               lista = EstadisticaHelper.executeStore("usp_reporteTotalAtencionesSP", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });

            }

            if ("15".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "descripcion" };
                IsExportIncludesHeader = false;

                lista = consultarDistribucionGenero(fechaInicio, fechaFin, region);

            }

            if ("18".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "region", "dependencia" };
                IsExportIncludesHeader = false;

                lista = consultarRegionGenero(fechaInicio, fechaFin, region, dependencia);

            }
            if ("19".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "region", "dependencia" };
                CatalogoHelper helper = new CatalogoHelper();
                List<ORIDetCompendio> idiomas = helper.getCatalogoDetalleList(helper._TIPO_IDIOMA);
                List<String> headersList = new List<string>();// { "Región", "Espacio de Atención" };
                headersList.Add("Región");
                headersList.Add("Espacio de Atención");
                foreach (ORIDetCompendio idioma in idiomas)
                {
                    headersList.Add(idioma.vNomCorto);
                }
                foreach (ORIDetCompendio idioma in idiomas)
                {
                    headersList.Add(idioma.vNomCorto + "%");
                }

                headers = headersList.ToArray<String>();

                IsExportIncludesHeader = true;

                lista = consultarRegionIdioma(fechaInicio, fechaFin, region);

            }

            if ("20".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "region", "dependencia" };
                IsExportIncludesHeader = false;

                lista = consultarRegionDiscapacidad(fechaInicio, fechaFin, region);

            }

            if ("21".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "region", "dependencia" };
                IsExportIncludesHeader = false;

                lista = consultarRegionIndocumentados(fechaInicio, fechaFin, region);

            }

            if ("22".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "usuario" };
                IsExportIncludesHeader = false;
                //tipo = "6";
                lista = consultarTipoSolicitanteGenero(fechaInicio, fechaFin, region);

            }


            if ("23".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "usuario" };
                CatalogoHelper helper = new CatalogoHelper();
                List<ORIDetCompendio> idiomas = helper.getCatalogoDetalleList(helper._TIPO_IDIOMA);
                List<String> headersList = new List<string>();// { "Región", "Espacio de Atención" };
                headersList.Add("Tipo de Solicitante");
                foreach (ORIDetCompendio idioma in idiomas)
                {
                    headersList.Add(idioma.vNomCorto);
                }
                foreach (ORIDetCompendio idioma in idiomas)
                {
                    headersList.Add(idioma.vNomCorto + "%");
                }

                headers = headersList.ToArray<String>();

                IsExportIncludesHeader = true;

                lista = consultarTipoSolicitanteIdioma(fechaInicio, fechaFin, region);

            }

            if ("24".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "usuario" };
                IsExportIncludesHeader = false;
                //tipo = "6";
                lista = consultarTipoSolicitanteDiscapacidad(fechaInicio, fechaFin, region);

            }


            if ("25".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "usuario" };
                IsExportIncludesHeader = false;
                //tipo = "6";
                lista = consultarTipoSolicitanteIndocumentado(fechaInicio, fechaFin, region);

            }
            if ("26".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "programa" };
                IsExportIncludesHeader = false;
                lista = consultarProgramaGenero(fechaInicio, fechaFin, region);

            } 
            if ("27".Equals(tipo))/*por genero */
            {
                lista = consultarProgramaIdioma(fechaInicio, fechaFin, region);

                etiquetas = new string[] { "programa" };

                headers = getColumnsTitulo(lista);

                IsExportIncludesHeader = true;

            }

            if ("28".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "programa" };
                IsExportIncludesHeader = false;
                //tipo = "6";
                lista = consultarProgramaIndocumentado(fechaInicio, fechaFin, region);

            }

            if ("29".Equals(tipo))/*por genero */
            {
                etiquetas = new string[] { "tipo" };
                IsExportIncludesHeader = false;
                lista = consultaTipoSolicitudGenero(fechaInicio, fechaFin, region);

            }


            if ("30".Equals(tipo))/*por genero */
            {
                lista = consultarTipoSolicitudIdioma(fechaInicio, fechaFin, region);

                etiquetas = new string[] { "tipo" };

                headers = getColumnsTitulo(lista);

                IsExportIncludesHeader = true;

            }

            if ("31".Equals(tipo))/*por region - espacio atencion*/
            {
                etiquetas = new string[] { "region"};
                lista = consultarRegionTipoSolicitud(fechaInicio, fechaFin, region, dependencia);

            }

            if ("100".Equals(tipo))/*por region - espacio atencion*/
            {
                lista = consultarPrueba(fechaInicio, fechaFin, region, dependencia);

                etiquetas = new string[] { "vdescripcion" };

                headers = getColumnsTitulo(lista);

                IsExportIncludesHeader = true;
                tipo = "30";

            }

            try
            {
                ExcelAppExportor exportor = new ExcelAppExportor();
                //mvalle 
                exportor.BindDataList = lista;
                string tempPath = System.IO.Path.GetTempPath();


                DateTime d = DateTime.Now;
                String strExportFileName = tempPath + "Template" + d.ToUniversalTime().Ticks + ".xlsx";
                //String strExportFileName = "c:\\proyectos\\Template" + d.ToUniversalTime().Ticks + ".xlsx";
                exportor.ExportFileName = strExportFileName;

                string miCarpeta = Server.MapPath("~/");
                exportor.TemplateFileName = miCarpeta + "Views\\Estadistica\\Template" + tipo + ".xlsx";
                //exportor.TemplateFileName = "c:\\proyectos\\Template" + tipo + ".xlsx";
                exportor.IsExportIncludesHeader = IsExportIncludesHeader;
                exportor.ExportSheetName = "Orienta";
                exportor.ListHeader = headers;
                exportor.Etiquetas = etiquetas;
                exportor.Export();


                byte[] bindata = System.IO.File.ReadAllBytes(strExportFileName);
                return File(bindata, "application/vnd.ms-excel", "RMidisOrienta" + tipo + ".xlsx");

            }
            catch (Exception ex)
            {
                String error = ex.ToString();
                Response.Write(error);

            }
            return null;
        }



        public ActionResult Reporte()
        {
            String fechaInicio = Request["fechaInicio"];
            String fechaFin = Request["fechaFin"];
            String tipo = Request["tipo"];
            String region = Request["region"];
            String dependencia = Request["dependencia"];
            String programa = Request["programa"];

            if ("1".Equals(tipo))/*por region - espacio atencion*/
            {
                IList<dynamic> lista = consultarClaseRegionDependencia(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle01", lista);
            }

            if ("4".Equals(tipo))/*por programa - clase */
            {
                IList<dynamic> lista = consultarTipoSolicitudRegion(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                {
                    String[] columnas = getColumnsDynamic(lista);
                    String[] titulos = getColumnsTitulo(lista);

                    ViewBag.columnas = columnas;
                    ViewBag.titulos = titulos;
                    return View("Detalle04", lista);
                }
            }


            if ("2".Equals(tipo))/*por region - clase*/
            {
                List<ReporteClase> lista = consultarClaseRegion(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle02", lista);
            }

            if ("3".Equals(tipo))/*por programa - clase */
            {
                IList<dynamic> lista = consultarProgramaClase(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                {
                    String[] columnas = getColumnsDynamic(lista);
                    String[] titulos = getColumnsTitulo(lista);

                    ViewBag.columnas = columnas;
                    ViewBag.titulos = titulos;
                    return View("Detalle03", lista);
                }
            }

            if ("5".Equals(tipo))/*casos derivados o por derivar - region -dependencia */
            {
                List<ReporteCasos> lista = consultarCasosRegionDependencia(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle05", lista);
            }
            if ("6".Equals(tipo))/*casos - Programa */
            {
                List<ReporteCasos> lista = consultarCasosPrograma(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle06", lista);
            }

            if ("7".Equals(tipo))/*por casos resueltos - region */
            {
                List<ReporteCasos> lista = consultarCasosRegionDependencia(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle07", lista);
            }
            if ("8".Equals(tipo))/*por casos resueltos - programa */
            {
                List<ReporteCasos> lista = consultarCasosPrograma(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle08", lista);
            }
            if ("9".Equals(tipo))/*por casos URGENTES - programa */
            {
                IList<dynamic> lista = consultarCasosUrgentesPrograma(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle09", lista);
            }
            if ("10".Equals(tipo))/*por casos URGENTES - programa */
            {
                IList<dynamic> lista = consultarCasosUrgentesEspacio(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle10", lista);
            }
            if ("11".Equals(tipo))/*por casos URGENTES - programa */
            {
                IList<dynamic> lista = consultarNumeroDias(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle11", lista);
            }
            if ("12".Equals(tipo))/*por casos URGENTES - programa */
            {
                IList<dynamic> lista = consultarTotalAtenciones(fechaInicio, fechaFin, region, programa);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle12", lista);
            }
            if ("15".Equals(tipo))/*por casos URGENTES - programa */
            {
                IList<dynamic> lista = consultarDistribucionGenero(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle15", lista);
            }

            if ("17".Equals(tipo))/*por region - genero*/
            {
                List<ReportePrograma> lista = consultarRegion(fechaInicio, fechaFin, tipo);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle17", lista);
            }

            if ("18".Equals(tipo))/*por region - genero*/
            {
                IList<dynamic> lista = consultarRegionGenero(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle18", lista);
            }

            if ("19".Equals(tipo))/*por region - genero*/
            {
                CatalogoHelper helper = new CatalogoHelper();

                List<ORIDetCompendio> idiomas = helper.getCatalogoDetalleList(helper._TIPO_IDIOMA);

                ViewBag.idiomas = idiomas;

                IList<dynamic> lista = consultarRegionIdioma(fechaInicio, fechaFin, tipo);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle19", lista);
            }

            if ("20".Equals(tipo))/*por region - genero*/
            {
                IList<dynamic> lista = consultarRegionDiscapacidad(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle20", lista);
            }

            if ("21".Equals(tipo))/*por region - genero*/
            {
                IList<dynamic> lista = consultarRegionIndocumentados(fechaInicio, fechaFin, tipo);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle21", lista);
            }
            if ("22".Equals(tipo))/*por region - genero*/
            {
                IList<dynamic> lista = consultarTipoSolicitanteGenero(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle22", lista);
            }

            if ("23".Equals(tipo))/*por region - genero*/
            {
                CatalogoHelper helper = new CatalogoHelper();

                List<ORIDetCompendio> idiomas = helper.getCatalogoDetalleList(helper._TIPO_IDIOMA);

                ViewBag.idiomas = idiomas;

                IList<dynamic> lista = consultarTipoSolicitanteIdioma(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle23", lista);
            }

            if ("24".Equals(tipo))/*por discapacidad - tipo de usuario*/
            {
                IList<dynamic> lista = consultarTipoSolicitanteDiscapacidad(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle24", lista);
            }
            if ("25".Equals(tipo))/*por discapacidad - tipo de usuario*/
            {
                IList<dynamic> lista = consultarTipoSolicitanteIndocumentado(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle25", lista);
            }

            if ("26".Equals(tipo))/*por genero */
            {
                IList<dynamic> lista = consultarProgramaGenero(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle26", lista);

            }



            if ("27".Equals(tipo))/*por genero */
            {
                IList<dynamic> lista = consultarProgramaIdioma(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                {
                    String[] columnas = getColumnsDynamic(lista);
                    String[] titulos = getColumnsTitulo(lista);

                    ViewBag.columnas = columnas;
                    ViewBag.titulos = titulos;
                    return View("Detalle27", lista);
                }

            }

            if ("28".Equals(tipo))/*por genero */
            {
                IList<dynamic> lista = consultarProgramaIndocumentado(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle28", lista);

            }
            if ("29".Equals(tipo))/*por genero */
            {
                IList<dynamic> lista = consultaTipoSolicitudGenero(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle29", lista);

            }

            if ("30".Equals(tipo))/*por genero */
            {
                IList<dynamic> lista = consultarTipoSolicitudIdioma(fechaInicio, fechaFin, region);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                {
                    String[] columnas = getColumnsDynamic(lista);
                    String[] titulos = getColumnsTitulo(lista);

                    ViewBag.columnas = columnas;
                    ViewBag.titulos = titulos;
                    return View("Detalle30", lista);
                }

            }


            if ("31".Equals(tipo))/*por region - espacio atencion*/
            {
                IList<dynamic> lista = consultarRegionTipoSolicitud(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                    return View("Detalle31", lista);
            }

            if ("100".Equals(tipo))/*por region - espacio atencion*/
            {
                IList<dynamic> lista = consultarPrueba(fechaInicio, fechaFin, region, dependencia);
                if (lista == null || lista.Count == 0)
                    return View("Mensaje");
                else
                {
                    String[] columnas = getColumnsDynamic(lista);
                    String[] titulos = getColumnsTitulo(lista);

                    ViewBag.columnas = columnas;
                    ViewBag.titulos = titulos;
                    return View("Detalle00", lista);
                }
            }

            return View("Mensaje");
           
        }


        //00
        private IList<dynamic> consultarPrueba(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reportePrueba", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });

           // return EstadisticaHelper.executeStore("usp_reportePrueba", new String[] { fechaInicio, fechaFin, region, dependencia });
        }


        private IList<dynamic> consultarClaseRegionDependencia(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteClaseRegionDependencia", new String[] { fechaInicio, fechaFin, region, dependencia });
        }

        //4
        private IList<dynamic> consultarTipoSolicitudRegion(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            // return EstadisticaHelper.executeStore("usp_reporteProgramaClase", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });

            return EstadisticaHelper.executeStore("usp_reporteTipoSolicitudRegion", new String[] { fechaInicio, fechaFin, region, "" });
        }

        private IList<dynamic> consultarClaseRegionSP(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteClaseRegion", new String[] { fechaInicio, fechaFin, region, "" });
        }
        //3
        private IList<dynamic> consultarProgramaClase(String fechaInicio, String fechaFin, String region, String dependencia)
        {
           // return EstadisticaHelper.executeStore("usp_reporteProgramaClase", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
   
             return EstadisticaHelper.executeStore("usp_reporteProgramaClase", new String[] { fechaInicio, fechaFin, "", "" });
        }

        //5
        private IList<dynamic> consultarCasosRegionDependenciaSP(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteCasosRegionDependencia", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
            //return EstadisticaHelper.executeStore("usp_reporteCasosRegionDependencia", new String[] { fechaInicio, fechaFin, region, dependencia });//, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //6

        private IList<dynamic> consultarCasosProgramaSP(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteCasosPrograma", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //7
        private IList<dynamic> consultarCasosResueltosEspacioAtencion(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteCasosResueltosEspacioAtencion", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }
        //8
        private IList<dynamic> consultarCasosResueltoPrograma(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteCasosResueltoPrograma", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }



        private IList<dynamic> consultarCasosUrgentesPrograma(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteCasosUrgentes", new String[] { fechaInicio, fechaFin, region, "" });
        }

        private IList<dynamic> consultarCasosUrgentesEspacio(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteCasosUrgentesEspacio", new String[] { fechaInicio, fechaFin, region, dependencia });
        }

        private IList<dynamic> consultarNumeroDias(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteNumeroDias", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }
        //12
        private IList<dynamic> consultarTotalAtenciones(String fechaInicio, String fechaFin, String region, String programa)
        {
            return EstadisticaHelper.executeStore("usp_reporteTotalAtenciones", new String[] { fechaInicio, fechaFin, region, programa });
        }
        private IList<dynamic> consultarDistribucionGenero(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteDistribucionGenero", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }
        //18
        private IList<dynamic> consultarRegionGenero(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteRegionGenero", new String[] { fechaInicio, fechaFin, region, dependencia }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }
        //19
        private IList<dynamic> consultarRegionIdioma(String fechaInicio, String fechaFin, String tipo)
        {
            return EstadisticaHelper.executeStore("usp_reporteRegionIdioma", new String[] { fechaInicio, fechaFin, "", "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //20
        private IList<dynamic> consultarRegionDiscapacidad(String fechaInicio, String fechaFin, String tipo)
        {
            return EstadisticaHelper.executeStore("usp_reporteRegionDiscapacidad", new String[] { fechaInicio, fechaFin, "", "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //21
        private IList<dynamic> consultarRegionIndocumentados(String fechaInicio, String fechaFin, String tipo)
        {
            return EstadisticaHelper.executeStore("usp_reporteRegionIndocumentados", new String[] { fechaInicio, fechaFin, "", "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }


        //22
        private IList<dynamic> consultarTipoSolicitanteGenero(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteTipoSolicitanteGenero", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }
        //23
        private IList<dynamic> consultarTipoSolicitanteIdioma(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteTipoSolicitanteIdioma", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //24
        private IList<dynamic> consultarTipoSolicitanteDiscapacidad(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteTipoSolicitanteDiscapacidad", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //25
        private IList<dynamic> consultarTipoSolicitanteIndocumentado(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteTipoSolicitanteIndocumentado", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }


        //26
        private IList<dynamic> consultarProgramaGenero(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteProgramaGenero", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //27
        private IList<dynamic> consultarProgramaIdioma(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteProgramaIdioma", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //28
        private IList<dynamic> consultarProgramaIndocumentado(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteProgramaIndocumentado", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }


        //29
        private IList<dynamic> consultaTipoSolicitudGenero(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteTipoSolicitudGenero", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }


        //30
        private IList<dynamic> consultarTipoSolicitudIdioma(String fechaInicio, String fechaFin, String region)
        {
            return EstadisticaHelper.executeStore("usp_reporteTipoSolicitudIdioma", new String[] { fechaInicio, fechaFin, region, "" }, new String[] { "fechaInicio", "fechaFin", "region", "dependencia" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar, System.Data.SqlDbType.NVarChar });
        }

        //31
        private IList<dynamic> consultarRegionTipoSolicitud(String fechaInicio, String fechaFin, String region, String dependencia)
        {
            return EstadisticaHelper.executeStore("usp_reporteRegionTipoSolicitud", new String[] { fechaInicio, fechaFin, region, dependencia });
        }


        private List<ReporteClase> consultarClaseRegionDependencia1(String fechaInicio, String fechaFin, String tipo)
        {

            String sql = sqlClaseRegionDependencia(fechaInicio, fechaFin);

            List<ReporteClase> lista = db.ExecuteStoreQuery<ReporteClase>(sql).AsQueryable().ToList();
            decimal total = obtenerTotalClase(lista, 1);
            foreach (ReporteClase rep in lista)
            {
                rep.sugerencia_ = rep.sugerencia / total;
                rep.consulta_ = rep.consulta / total;
                rep.pedido_ = rep.pedido / total;
                rep.reclamo_ = rep.reclamo / total;
                rep.queja_ = rep.queja / total;
                rep.denuncia_ = rep.denuncia / total;
            }
            return lista;
        }

        private List<ReporteClase> consultarClaseRegion(String fechaInicio, String fechaFin, String region)
        {

            String sql = null;

            sql = sqlClaseRegion(fechaInicio, fechaFin, region);


            List<ReporteClase> lista = db.ExecuteStoreQuery<ReporteClase>(sql).AsQueryable().ToList();
            decimal total = obtenerTotalClase(lista, 0);
            foreach (ReporteClase rep in lista)
            {

                rep.sugerencia_ = rep.sugerencia / total;
                rep.consulta_ = rep.consulta / total;
                rep.pedido_ = rep.pedido / total;
                rep.reclamo_ = rep.reclamo / total;
                rep.queja_ = rep.queja / total;
                rep.denuncia_ = rep.denuncia / total;
            }
            return lista;
        }


        private List<ReporteCasos> consultarCasosRegionDependencia(String fechaInicio, String fechaFin, String region, String dependencia)
        {

            String sql = sqlCasosRegionDependencia(fechaInicio, fechaFin, region, dependencia);

            List<ReporteCasos> lista = db.ExecuteStoreQuery<ReporteCasos>(sql).AsQueryable().ToList();
            decimal total = lista.Last<ReporteCasos>().total();
            foreach (ReporteCasos rep in lista)
            {
                rep.temporalmente_ = rep.temporalmente / total;
                rep.proceso_ = rep.proceso / total;
                rep.final_ = rep.final / total;
            }
            return lista;
        }

        private List<ReporteCasos> consultarCasosPrograma(String fechaInicio, String fechaFin, String region)
        {

            String sql = sqlCasosPrograma(fechaInicio, fechaFin, region);

            List<ReporteCasos> lista = db.ExecuteStoreQuery<ReporteCasos>(sql).AsQueryable().ToList();
            decimal total = lista.Last<ReporteCasos>().total();
            foreach (ReporteCasos rep in lista)
            {
                rep.temporalmente_ = rep.temporalmente / total;
                rep.proceso_ = rep.proceso / total;
                rep.final_ = rep.final / total;
            }
            return lista;
        }


        private List<ReportePrograma> consultarProgramaClase(String fechaInicio, String fechaFin, String tipo)
        {

            String sql = sqlProgramaClase(fechaInicio, fechaFin);
            ViewBag.sql = sql;
            //return null;


            List<ReportePrograma> lista = db.ExecuteStoreQuery<ReportePrograma>(sql).AsQueryable().ToList();
            decimal total = obtenerTotal(lista, 0);
            foreach (ReportePrograma rep in lista)
            {
                rep.pension_ = rep.pension / total;
                rep.beca_ = rep.beca / total;
                rep.vaso_ = rep.vaso / total;
                rep.pronaa_ = rep.pronaa / total;
                rep.cuna_ = rep.cuna / total;
                rep.qali_ = rep.qali / total;
            }
            return lista;
        }

        private List<ReportePrograma> consultarRegion(String fechaInicio, String fechaFin, String tipo)
        {

            String sql = null;
            if (tipo.Equals("17"))
                sql = sqlEspacioAtencionGenero(fechaInicio, fechaFin);
            if (tipo.Equals("4"))
                sql = sql4(fechaInicio, fechaFin);


            List<ReportePrograma> lista = db.ExecuteStoreQuery<ReportePrograma>(sql).AsQueryable().ToList();
            decimal total = obtenerTotal(lista, 0);
            foreach (ReportePrograma rep in lista)
            {
                rep.pension_ = rep.pension / total;
                rep.beca_ = rep.beca / total;
                rep.vaso_ = rep.vaso / total;
                rep.pronaa_ = rep.pronaa / total;
                rep.cuna_ = rep.cuna / total;
                rep.qali_ = rep.qali / total;
            }
            return lista;
        }

        private decimal obtenerTotal(List<ReportePrograma> lista, int posicion)
        {
            ReportePrograma reporte = (posicion == 0) ? lista.First<ReportePrograma>() : lista.Last<ReportePrograma>();
            return reporte.beca + reporte.cuna + reporte.pension + reporte.pronaa + reporte.qali + reporte.vaso;
        }

        private decimal obtenerTotalClase(List<ReporteClase> lista, int posicion)
        {
            ReporteClase reporte = (posicion == 0) ? lista.First<ReporteClase>() : lista.Last<ReporteClase>();
            return reporte.total();
        }

        private String sqlEspacioAtencionGenero(String fechaInicio, String fechaFin)
        {

            StringBuilder sql = new StringBuilder("SELECT t.dependencia, t.genero, ");
            sql.Append("SUM(t.pension) as pension, ");
            sql.Append("SUM(t.beca) as beca, ");
            sql.Append("SUM(t.vaso) as vaso, ");
            sql.Append("SUM(t.pronaa) as pronaa, ");
            sql.Append("SUM(t.cuna) as cuna,  ");
            sql.Append("SUM(qali)  as qali from ( ");
            sql.Append("select d.vDescripcion as dependencia,c.vNomCorto as genero ,nIdeClaseSolicitud, ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 1 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'pension', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 2 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'beca', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 3 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'vaso', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 41 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'pronaa', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 42 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'cuna', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 43 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'foncodes', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 44 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'qali' ");
            sql.Append("from ORIMaeSolicitud s inner join ORIMaePersona p on p.nIdePersona = s.nIdePersonSolicita ");
            sql.Append("inner join ORIMaeDependencia d1 on d1.nIdeDep=s.nIdeDep ");
            sql.Append("inner join ORIMaeDependencia d on d.nIdeDep=d1.nIdeDepRegion ");
            sql.Append("inner join ORIDetCompendio c on c.nIdeDetalle= p.nIdeTipoGenero  ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            sql.Append(") t ");
            sql.Append("group by ");
            sql.Append("t.dependencia, t.genero with rollup ");
            sql.Append("order by dependencia, genero");
            return sql.ToString();
        }


        private String sqlEspacioAtencionIdioma(String fechaInicio, String fechaFin)
        {
            CatalogoHelper helper = new CatalogoHelper();
            List<ORIDetCompendio> idiomas = orienta.Utils.Cache.getListCompendio(helper._TIPO_IDIOMA);

            StringBuilder sql = new StringBuilder("SELECT t.dependencia, t.idioma ");
            foreach (ORIDetCompendio idioma in idiomas)
            {
                sql.Append(" ,SUM(t.").Append(idioma.vNomCorto).Append(") as ").Append(idioma.vNomCorto);
            }
            sql.Append(" from ( ");
            sql.Append("select d.vDescripcion as dependencia,c.vNomCorto as genero ,nIdeClaseSolicitud ");

            foreach (ORIDetCompendio idioma in idiomas)
            {
                sql.Append(",(SELECT  ");
                sql.Append("   CASE  ");
                sql.Append("      WHEN s.nIdeIdioma = ").Append(idioma.nIdeDetalle.ToString()).Append(" THEN 1 ");
                sql.Append("      else 0  ");
                sql.Append("   END) as '").Append(idioma.vNomCorto).Append("' ");

            }
            sql.Append("from ORIMaeSolicitud s inner join ORIMaePersona p on p.nIdePersona = s.nIdePersonSolicita ");
            sql.Append("inner join ORIMaeDependencia d1 on d1.nIdeDep=s.nIdeDep ");
            sql.Append("inner join ORIMaeDependencia d on d.nIdeDep=d1.nIdeDepRegion ");
            sql.Append("inner join ORIDetCompendio c on c.nIdeDetalle= p.nIdeTipoGenero  ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            sql.Append(") t ");
            sql.Append("group by ");
            sql.Append("t.dependencia, t.genero with rollup ");
            sql.Append("order by dependencia, genero");
            return sql.ToString();
        }


        /*por region*/
        private String sql4(String fechaInicio, String fechaFin)
        {

            StringBuilder sql = new StringBuilder("SELECT t.dependencia, ");
            sql.Append("SUM(t.pension) as pension, ");
            sql.Append("SUM(t.beca) as beca, ");
            sql.Append("SUM(t.vaso) as vaso, ");
            sql.Append("SUM(t.pronaa) as pronaa, ");
            sql.Append("SUM(t.cuna) as cuna,  ");
            sql.Append("SUM(qali)  as qali from ( ");
            sql.Append("select d.vDescripcion as dependencia,nIdeClaseSolicitud, ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 1 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'pension', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 2 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'beca', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 3 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'vaso', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 41 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'pronaa', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 42 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'cuna', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 43 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'foncodes', ");
            sql.Append("(SELECT  ");
            sql.Append("   CASE  ");
            sql.Append("      WHEN s.nIdePrograma = 44 THEN 1 ");
            sql.Append("      else 0  ");
            sql.Append("   END) as 'qali' ");
            sql.Append("from ORIMaeSolicitud s  ");
            sql.Append("inner join ORIMaeDependencia d1 on d1.nIdeDep=s.nIdeDep ");
            sql.Append("inner join ORIMaeDependencia d on d.nIdeDep=d1.nIdeDepRegion ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            sql.Append(") t ");
            sql.Append("group by ");
            sql.Append("t.dependencia with rollup ");
            sql.Append("order by dependencia");
            return sql.ToString();
        }


        /*Por region y clase*/
        private String sqlClaseRegion(String fechaInicio, String fechaFin, String region)
        {
            StringBuilder sql = new StringBuilder("SELECT t.dependencia,  ");
            sql.Append("SUM(t.sugerencia) as sugerencia,  ");
            sql.Append("SUM(t.consulta) as consulta,  ");
            sql.Append("SUM(t.pedido) as pedido,  ");
            sql.Append("SUM(t.reclamo) as reclamo,  ");
            sql.Append("SUM(t.queja) as queja,   ");
            sql.Append("SUM(t.denuncia)  as denuncia  ");
            sql.Append("from ( select d.vDescripcion as dependencia ,nIdeClaseSolicitud,  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 45 THEN 1 else 0 END) as 'sugerencia',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 46 THEN 1 else 0 END) as 'consulta',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 47 THEN 1 else 0 END) as 'pedido',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 48 THEN 1 else 0 END) as 'reclamo',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 49 THEN 1 else 0 END) as 'queja',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 50 THEN 1 else 0 END) as 'denuncia' ");
            sql.Append("from  ");
            sql.Append("ORIMaeSolicitud s  ");
            sql.Append("inner join ORIMaePersona p on p.nIdePersona = s.nIdePersonSolicita  ");
            sql.Append("inner join ORIMaeDependencia d1 on d1.nIdeDep=s.nIdeDep  ");
            sql.Append("inner join ORIMaeDependencia d on d.nIdeDep=d1.nIdeDepRegion ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            if (!region.Equals("0"))
                sql.Append("and d.nIdeDep ='").Append(region).Append("'");
            sql.Append(") t  ");
            sql.Append("group by  t.dependencia   with rollup ");
            sql.Append("order by t.dependencia  ");

            return sql.ToString();

        }

        /*Por region y clase*/
        private String sqlClaseRegionDependencia(String fechaInicio, String fechaFin)
        {
            StringBuilder sql = new StringBuilder("SELECT isnull(t.region,'ztotal') as region,  isnull(t.dependencia,'ztotal') as dependencia,  ");
            sql.Append("SUM(t.sugerencia) as sugerencia,  ");
            sql.Append("SUM(t.consulta) as consulta,  ");
            sql.Append("SUM(t.pedido) as pedido,  ");
            sql.Append("SUM(t.reclamo) as reclamo,  ");
            sql.Append("SUM(t.queja) as queja,   ");
            sql.Append("SUM(t.denuncia)  as denuncia  ");
            sql.Append("from ( select r.vDescripcion as region ,d.vDescripcion as dependencia ,nIdeClaseSolicitud,  ");
            sql.Append("(select case when s.nIdeClaseSolicitud = 45 then 1 else 0 end ) sugerencia,  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 46 THEN 1 else 0 END ) as 'consulta',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 47 THEN 1 else 0 END ) as 'pedido',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 48 THEN 1 else 0 END ) as 'reclamo',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 49 THEN 1 else 0 END ) as 'queja',  ");
            sql.Append("(SELECT CASE WHEN s.nIdeClaseSolicitud = 50 THEN 1 else 0 END ) as 'denuncia' ");
            sql.Append("from  ");
            sql.Append("ORIMaeSolicitud s  ");
            sql.Append("inner join ORIMaePersona p on p.nIdePersona = s.nIdePersonSolicita  ");
            sql.Append("inner join ORIMaeDependencia d on d.nIdeDep=s.nIdeDep  ");
            sql.Append("inner join ORIMaeDependencia r on r.nIdeDep=d.nIdeDepRegion ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            sql.Append(") t  ");
            sql.Append("group by  t.region, t.dependencia   with rollup ");
            //sql.Append("order by t.region,t.dependencia  ");
            return "select region, dependencia, sugerencia,consulta, pedido, reclamo, queja, denuncia from(" + sql.ToString() + ")t2 order by region, dependencia";

            //return sql.ToString();

        }

        /*ProgramaClase*/
        private String sqlProgramaClase(String fechaInicio, String fechaFin)
        {

            StringBuilder sql = new StringBuilder("SELECT t.claseSolicitud, ");
            sql.Append("SUM(t.pension) as pension, SUM(t.beca) as beca, SUM(t.vaso) as vaso, SUM(t.pronaa) as pronaa, SUM(t.cuna) as cuna,  SUM(qali)  as qali  ");
            sql.Append("from ( select c.vNomCorto as claseSolicitud,  ");
            sql.Append("(SELECT     CASE        WHEN s.nIdePrograma = 1 THEN 1       else 0     END) as 'pension', (SELECT     CASE        WHEN s.nIdePrograma = 2 THEN 1       else 0     END) as 'beca', (SELECT     CASE        WHEN s.nIdePrograma = 3 THEN 1       else 0     END) as 'vaso', (SELECT     CASE        WHEN s.nIdePrograma = 41 THEN 1       else 0     END) as 'pronaa', (SELECT     CASE        WHEN s.nIdePrograma = 42 THEN 1       else 0     END) as 'cuna', (SELECT     CASE        WHEN s.nIdePrograma = 43 THEN 1       else 0     END) as 'foncodes', (SELECT     CASE        WHEN s.nIdePrograma = 44 THEN 1       else 0     END) as 'qali'  ");
            sql.Append("from ORIMaeSolicitud s  ");
            sql.Append("inner join ORIMaePersona p on p.nIdePersona = s.nIdePersonSolicita  ");
            sql.Append("inner join ORIDetCompendio c on c.nIdeDetalle= s.nIdeClaseSolicitud  ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            sql.Append("   ) t  ");
            sql.Append("group by t.claseSolicitud with rollup ");

            return "select * from(" + sql.ToString() + ")t2 order by claseSolicitud";

        }


        /*casos region dependencia*/
        private String sqlCasosRegionDependencia(String fechaInicio, String fechaFin, String region, String dependencia)
        {

            StringBuilder sql = new StringBuilder("SELECT isnull(t.region,'ztotal') as region,  isnull(t.dependencia,'ztotal') as dependencia,  ");
            sql.Append("SUM(t.temporalmente) as temporalmente,   ");
            sql.Append("SUM(t.final) as final,   ");
            sql.Append("SUM(t.proceso) as proceso  ");
            sql.Append("from (   ");
            sql.Append("select r.vDescripcion as region,d.vDescripcion as dependencia,nIdeClaseSolicitud,   ");
            sql.Append("(SELECT     CASE        WHEN s.nideestadosol = 74 THEN 1       else 0     END) as 'temporalmente',   ");
            sql.Append("(SELECT     CASE        WHEN s.nideestadosol = 75 THEN 1       else 0     END) as 'final',   ");
            sql.Append("(SELECT     CASE        WHEN s.nideestadosol = 76 THEN 1       else 0     END) as 'proceso'  ");
            sql.Append("from   ");
            sql.Append("ORIMaeSolicitud s inner join ORIMaeDependencia d on d.nIdeDep=s.nIdeDep   ");
            sql.Append("inner join ORIMaeDependencia r on r.nIdeDep=d.nIdeDepRegion  ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            if (!region.Equals("0"))
                sql.Append("and r.nIdeDep ='").Append(region).Append("'");
            if (!dependencia.Equals("0"))
                sql.Append("and d.nIdeDep ='").Append(dependencia).Append("'");
            sql.Append(") t   ");
            sql.Append("group by  t.region, t.dependencia with rollup  ");
            //sql.Append("order by t.region,t.dependencia  ");

            return "select * from(" + sql.ToString() + ")t2 order by region,dependencia";

        }

        /*casos - programa*/
        private String sqlCasosPrograma(String fechaInicio, String fechaFin, String region)
        {

            StringBuilder sql = new StringBuilder("SELECT isnull(t.programa,'ztotal') as programa,  ");
            sql.Append("SUM(t.temporalmente) as temporalmente,   ");
            sql.Append("SUM(t.final) as final,   ");
            sql.Append("SUM(t.proceso) as proceso  ");
            sql.Append("from (   ");
            sql.Append("select c.vNomCorto as programa,   ");
            sql.Append("(SELECT     CASE        WHEN s.nideestadosol = 74 THEN 1       else 0     END) as 'temporalmente',   ");
            sql.Append("(SELECT     CASE        WHEN s.nideestadosol = 75 THEN 1       else 0     END) as 'final',   ");
            sql.Append("(SELECT     CASE        WHEN s.nideestadosol = 76 THEN 1       else 0     END) as 'proceso'  ");
            sql.Append("from   ");
            sql.Append("ORIMaeSolicitud s inner join ORIDetCompendio c on c.nIdeDetalle=s.nIdePrograma   ");
            sql.Append("inner join ORIMaeDependencia d on d.nIdeDep=s.nIdeDep   ");
            sql.Append("inner join ORIMaeDependencia r on r.nIdeDep=d.nIdeDepRegion  ");
            sql.Append("where convert(date,s.dtFecRegistro,103)>=convert(date, '" + fechaInicio + "', 103)  ");
            sql.Append("and   convert(date,s.dtFecRegistro,103)<=convert(date, '" + fechaFin + "', 103)  ");
            if (!region.Equals("0"))
                sql.Append("and r.nIdeDep ='").Append(region).Append("'");
            sql.Append(") t   ");
            sql.Append(" where ( t.proceso + t.temporalmente + t.final >0 ) ");
            sql.Append("group by  t.programa with rollup  ");

            return "select * from(" + sql.ToString() + ")t2 order by programa";

        }



        private String[] getColumnsDynamic(IList<dynamic> listaDinamica)
        {

            IEnumerator<dynamic> enumerator = listaDinamica.GetEnumerator();

            enumerator.MoveNext();
            dynamic current = enumerator.Current;
            dynamic columna = new System.Dynamic.ExpandoObject();
            var col1 = columna as IDictionary<String, object>;
            col1 = current;
            ICollection<String> keys = col1.Keys;
            return keys.ToArray<String>();
        }


        private String[] getColumnsTitulo(IList<dynamic> listaDinamica)
        {

            IEnumerator<dynamic> enumerator = listaDinamica.GetEnumerator();

            enumerator.MoveNext();
            dynamic current = enumerator.Current;
            dynamic columna = new System.Dynamic.ExpandoObject();
            var col1 = columna as IDictionary<String, object>;
            col1 = current;
            ICollection<String> keys = col1.Keys;
            List<String> keyList = new List<string>();
            String titulo;
            foreach(String key in keys){
                titulo = key.ToLower().Replace('_', ' ');
                keyList.Add(titulo.Substring(0,1).ToUpper() + titulo.Substring(1) );
            }
            return keyList.ToArray<String>();
        }

    }



}
